package wangm.dbutil;

import wangm.bean.ExaminationPaper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.tools.ant.taskdefs.SQLExec;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * describe
 *
 * @author zk
 * @date 2018-02-23 14:11
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:dispatcher.xml")
public class DBUtilTest {
    @Autowired
    private QueryRunner queryRunner;

    @Test
    public void createTest() throws SQLException {
        String createTableSql = "create table t_user1(uid varchar(64) primary key,username varchar(32),password varchar(64))";
        int ret = queryRunner.update(createTableSql);
        System.out.println(ret);
    }

    @Test
    public void insertTable() throws SQLException {
        Object[] result;
        for (int i = 0; i < 10; i++) {
            String sql = "insert into t_user1 values(?,?,?)";
            result = queryRunner.insert(sql,new ArrayHandler(),getRandomStr(),getRandomStr(),getRandomStr());
        }
    }

    @Test
    public void lookTable() throws SQLException {
        List result = queryRunner.query("select * from t_user1",new MapListHandler());
        System.out.println(result);
    }

    @Test
    public void lookCombineTable() throws SQLException {
        String sql = "SELECT * " +
                "FROM T_EXAMINATION_PAPER p JOIN T_PAPER_TOPIC_COMBINE ptc ON (p.id = ptc.pid) JOIN T_EXAMINATION_TOPIC t  ON (t.id = ptc.tid)";
        List result = queryRunner.query(sql, new BeanListHandler<ExaminationPaper>(ExaminationPaper.class));
        System.out.println(result.size());
    }
    @Test
    public void initSql(){
        SQLExec sqlExec = new SQLExec();
    }

    @Test
    public void lookAllTable() throws SQLException {
        Map result = queryRunner.query("show tables",new MapHandler());
        System.out.println(result);
    }

    @Test
    public void deleteTable() throws SQLException {
        String delSql = "drop table t_user";
        int ret = queryRunner.update(delSql);
        System.out.println(ret);
    }

    public String getRandomStr(){
        return UUID.randomUUID().toString().substring(0,10);
    }
}
